#!/usr/bin/env python
# -*- encoding: utf-8 -*-
import sys
import ibm_db


class XDB2Utils(object):

    @staticmethod
    def db_connect(p_user= None, p_host= None, p_port = 50000, p_passwd= None, p_db_name= None):
        """

        :param p_user:
        :param p_host:
        :param p_port:
        :param p_passwd:
        :param p_db_name:
        :return:
        """
        success = False
        conn = None
        try:
            a = "DATABASE=%s;HOSTNAME=%s;PORT=%s;PROTOCOL=TCPIP;UID=%s;PWD=%s;" % (p_db_name,
                                                                                   p_host,
                                                                                   str(p_port),
                                                                                   p_user,
                                                                                   p_passwd)
            conn = ibm_db.connect(a, "", "")
            success = conn is not None
        except Exception as e:
            # print >> sys.stderr, "DB2Exception"#, str(e.message)
            import traceback
            traceback.print_exc()
            success = False
            conn = None
        return success, conn

    @staticmethod
    def db_close(p_conn=None):
        """

        :param p_conn:
        :return:
        """
        if p_conn is not None:
            try:
                ibm_db.close(p_conn)
            except Exception as e:
                # print >> sys.stderr, "DB2Exception", str(e)
                import traceback
                traceback.print_exc()

    @staticmethod
    def execute_sql(p_sql= None, p_conn=None):
        """
        执行SQL语句

        方法一：
        语法:
        调用ibm_db.prepare()函数并传递以下参数：
        1.从ibm_db.connect（）或ibm_db.pconnect（）函数返回的有效数据库连接资源；
        2.包含SQL语句的字符串，包括需要变量输入的任何列或谓词值的参数标记（？）。该字符串可以包含由XMLQUERY子句包装的XQuery表达式
        3.ibm_db.execute()执行SQL语句
        import ibm_db
        db_connect = ibm_db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username;PWD=password;", "", "")
        stmt = ibm_db.prepare(db_connect, "UPDATE employee SET bonus = '1000' WHERE job = 'MANAGER'")
        # 执行SQL语句
        ibm_db.execute(stmt)

        方法二：
        语法：
        调用ibm_db.exec_immediate（）函数并传递以下参数：
        1.从ibm_db.connect（）或ibm_db.pconnect（）函数返回的有效数据库连接资源；
        2.包含SQL语句的字符串。该字符串可以包含由XMLQUERY子句包装的XQuery表达式
        import ibm_db
        db_connect = ibm_db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username;PWD=password;", "", "")
        # 单条SQL语句
        stmt = ibm_db.exec_immediate(db_connect, "UPDATE employee SET bonus = '1000' WHERE job = 'MANAGER'")
        # 多条SQL语句
        sql = "SELECT XMLSERIALIZE(XMLQUERY('for $i in $t/address where $i/city = \"Olathe\" return <zip>{$i/zip/text()}</zip>' passing c.xmlcol as \"t\") AS CLOB(32k)) FROM xml_test c WHERE id = 1"
        stmt = ibm_db.exec_immediate(db_connect, sql)
        result = ibm_db.fetch_both(stmt)

        :param p_sql:
        :param p_conn:
        :return:
        """
        success = False
        if p_sql is not None:
            try:
                stmt = ibm_db.prepare(p_conn, p_conn)
                ibm_db.execute(stmt)
                success = True
            except Exception as e:
                import traceback
                traceback.print_exc()
                success = False
        return success

    @staticmethod
    def executemany_sql(p_sql= None, p_conn=None, p_param = None):
        """
        transaction

        https://www.cnblogs.com/staff/p/10023823.html

        :param p_sql:
        :param p_conn:
        :param p_param:
        :return:
        """
        success = False
        try:
            for o in p_param:
                tmp_sql = p_sql % o
                ibm_db.exec_immediate(p_conn, tmp_sql)
        except Exception as e:
            ibm_db.rollback(p_conn)
            import traceback
            traceback.print_exc()
        else:
            ibm_db.commit(p_conn)
        return success

    @staticmethod
    def fetchall_sql(p_sql= None, p_conn=None):
        """
        # NOTE https://blog.csdn.net/weixin_42970378/article/details/85564462

        SQL语句结果返回
        |-------------------------------|----------------------------------------------------------------
        |语句      	                    |描述
        |-------------------------------|----------------------------------------------------------------
        |ibm_db.fetch_tuple（）	        |返回按列位置索引的元组，表示结果集中的行。列是0索引的
        |-------------------------------|----------------------------------------------------------------
        |ibm_db.fetch_assoc（）	        |返回按列名索引的字典，表示结果集中的行
        |-------------------------------|----------------------------------------------------------------
        |ibm_db.fetch_both（）	        |返回一个字典，由列名和位置索引，表示结果集中的行
        |-------------------------------|----------------------------------------------------------------
        |ibm_db.fetch_row（）	        |将结果集指针设置为下一行或请求的行。使用此函数迭代结果集
        |-------------------------------|----------------------------------------------------------------

        import ibm_db
        db_connect = ibm_db.connect("DATABASE=name;HOSTNAME=host;PORT=60000;PROTOCOL=TCPIP;UID=username;PWD=password;", "", "")
        sql = "SELECT * FROM EMPLOYEE"
        stmt = ibm_db.exec_immediate(db_connect, sql)
        #
        # 通过调用ibm_db.fetch_tuple（）函数从结果集中获取行。
        tuple = ibm_db.fetch_tuple(stmt)
        while tuple != False:
            print "The ID is : ", tuple[0]
            print "The name is : ", tuple[1]
        #
        # 通过调用ibm_db.fetch_assoc（）函数从结果集中根据字典的键获取结果
        dictionary = ibm_db.fetch_assoc(stmt)
        while dictionary != False:
            print "The ID is : ", dictionary["EMPNO"]
        #
        # 通过调用ibm_db.fetch_both（）函数从结果集中获取行的结果或根据字典的键获取结果
        dictionary = ibm_db.fetch_both(stmt)
        while dictionary != False:
            print "The ID is : ",  dictionary["EMPNO"]
            print "The Name is : ", dictionary[1]
        #
        # 通过调用ibm_db.fetch_row（）函数从结果集中进行迭代获取结果
        while ibm_db.fetch_row(stmt) != False:
            print("The Employee number is : ",  ibm_db.result(stmt, 0))
            print("The Name is : ", ibm_db.result(stmt, "NAME"))
        #
        :param p_sql:
        :param p_conn:
        :return:
        """
        success = False
        results = list()
        try:
            stmt = ibm_db.exec_immediate(p_conn, p_sql)
            dictionary = ibm_db.fetch_both(stmt)
            # NOTE ibm_db没办法一次提取多行,提取多行数据,需要循环提取
            while dictionary != False:
                results.append(dictionary)
                dictionary = ibm_db.fetch_both(stmt)
            success = True
        except Exception as e:
            success = False
            results = list()
            import traceback
            traceback.print_exc()
        return success, results
